package com.focus.xyz.core.utils;


import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class ExcelUtils {
    private final static int READ_START_POS = 0;//默认的开始读取的行位置为第一行（索引值为0）

    private final static int READ_END_POS = 0;//默认结束读取的行位置为最后一行（索引值=0，用负数来表示倒数第n行）

    private final static int COMPARE_POS = 0;//默认Excel内容的开始比较列位置为第一列（索引值为0）

    private final static boolean NEED_COMPARE = true;//默认多文件合并的时需要做内容比较（相同的内容不重复出现）

    private final static boolean NEED_OVERWRITE = true;//默认多文件合并的新文件遇到名称重复时，进行覆盖

    private final static boolean ONLY_ONE_SHEET = true;//默认只操作一个sheet

    private final static int SELECTED_SHEET = 0;//默认读取第一个sheet中（只有当ONLY_ONE_SHEET = true时有效）

    private final static int READ_START_SHEET = 0;//默认从第一个sheet开始读取（索引值为0）

    private final static int READ_END_SHEET = 0;//默认在最后一个sheet结束读取（索引值=0，用负数来表示倒数第n行）

    private final static boolean PRINT_MSG = false;//默认打印各种信息

    private String excelPath = "test.xls";//默认Excel文件路径

    private int startReadPos = READ_START_POS;//设定开始读取的位置，默认为0

    private int endReadPos = READ_END_POS;//设定结束读取的位置，默认为0，用负数来表示倒数第n行

    private int comparePos = COMPARE_POS;//设定开始比较的列位置，默认为0

    private boolean isOverWrite = NEED_OVERWRITE;//设定汇总的文件是否需要替换，默认为true

    private boolean isNeedCompare = NEED_COMPARE;//设定是否需要比较，默认为true(仅当不覆写目标内容是有效，即isOverWrite=false时有效)

    private boolean onlyReadOneSheet = ONLY_ONE_SHEET;//设定是否只操作第一个sheet

    private int selectedSheetIdx = SELECTED_SHEET;//设定操作的sheet在索引值

    private String selectedSheetName = "";//设定操作的sheet的名称

    private int startSheetIdx = READ_START_SHEET;//设定开始读取的sheet id，默认为0

    private int endSheetIdx = READ_END_SHEET;//设定结束读取的sheet，默认为0，用负数来表示倒数第n行

    private boolean printMsg = PRINT_MSG;//设定是否打印消息

    private Workbook workbook;
    private Sheet sheet;

    public ExcelUtils() {

    }

    public ExcelUtils(String excelPath) {
        this.excelPath = excelPath;
    }

    /**
     * 还原设定（其实是重新new一个新的对象并返回）
     *
     * @return
     */
    public ExcelUtils RestoreSettings() {
        ExcelUtils instance = new ExcelUtils(this.excelPath);
        return instance;
    }

    /**
     * 从Excel文件得到二维数组，每个sheet的第一行为标题
     *
     * @param file Excel文件
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static String[][] getData(File file) throws FileNotFoundException,
            IOException {
        return getData(file, 1);
    }

    /**
     * 从Excel文件得到二维数组
     */
    public static String[][] getData(File file, int ignoreRows)
            throws FileNotFoundException, IOException {
        ArrayList result = new ArrayList();
        int rowSize = 0;
        BufferedInputStream in = new BufferedInputStream(new FileInputStream(
                file));
        // 打开HSSFWorkbook
        POIFSFileSystem fs = new POIFSFileSystem(in);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFCell cell = null;
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            HSSFSheet st = wb.getSheetAt(sheetIndex);
            System.out.println("Row size is " + st.getLastRowNum());
            // 第一行为标题，不取
            for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
                HSSFRow row = st.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                int tempRowSize = row.getLastCellNum() + 1;
                if (tempRowSize > rowSize) {
                    rowSize = tempRowSize;
                }
                String[] values = new String[rowSize];
                Arrays.fill(values, "");
                boolean hasValue = false;
                for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
                    String value = "";
                    cell = row.getCell(columnIndex);
                    if (cell != null) {
                        // 注意：一定要设成这个，否则可能会出现乱码
//                        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                        switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    if (date != null) {
                                        value = new SimpleDateFormat("yyyy-MM-dd")
                                                .format(date);
                                    } else {
                                        value = "";
                                    }
                                } else {
                                    value = new DecimalFormat("0").format(cell
                                            .getNumericCellValue());
                                }
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA:
                                // 导入时如果为公式生成的数据则无值
                                if (!cell.getStringCellValue().equals("")) {
                                    value = cell.getStringCellValue();
                                } else {
                                    value = cell.getNumericCellValue() + "";
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                value = "";
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                value = (cell.getBooleanCellValue() == true ? "Y"
                                        : "N");
                                break;
                            default:
                                value = "";
                        }
                    }
                    if (columnIndex == 0 && value.trim().equals("")) {
                        break;
                    }
                    values[columnIndex] = value;
                    hasValue = true;
                }

                if (hasValue) {
                    result.add(values);
                }
            }
        }
        in.close();
        String[][] returnArray = new String[result.size()][rowSize];
        for (int i = 0; i < returnArray.length; i++) {
            returnArray[i] = (String[]) result.get(i);
        }
        return returnArray;
    }

    /*public List<Row> readExcel() throws IOException {
        return readExcel(this.excelPath);
    }*/

    //以二维数组方式读取excel
    public String[][] readExcelArray() throws IOException {
        List<Row> list = readExcel(this.excelPath);
        String[][] rtn = new String[list.size()][];
        for (int i = 0; i < list.size(); i++) {
            Row row = list.get(i);
            String[] o = new String[row.getLastCellNum() - row.getFirstCellNum()];
            for (int m = 0; m < row.getLastCellNum(); m++) {
                o[m] = getCellValue(row.getCell(m));
            }
            rtn[i] = o;
        }
        return rtn;
    }


    //以二维数组方式读取excel,带入条件
    public String[][] readExcelArrayByCond(int index,String target) throws IOException {
        List<Row> list = readExcel(this.excelPath);
        List<Row> newList = new ArrayList<Row>();
        for(Row r :list){
            if(target.equals(getCellValue(r.getCell(index)))){
                newList.add(r);
                continue;
            }
        }
        String[][] rtn = new String[newList.size()][];
        for (int i = 0; i < newList.size(); i++) {
            Row row = newList.get(i);
            String[] o = new String[row.getLastCellNum() - row.getFirstCellNum()];
            for (int m = 0; m < row.getLastCellNum(); m++) {
                o[m] = getCellValue(row.getCell(m));
            }
            rtn[i] = o;
        }
        return rtn;
    }

    //以二维数组方式读取excel
    public String[][] readExcelArray(String filePath) throws IOException {
        this.setExcelPath(filePath);
        return  readExcelArray();
    }

    public List<Row> readExcel() throws IOException {
        return readExcel(this.excelPath);
    }

    public List<Row> readExcel(String xlsPath) throws IOException {
        //扩展名为空时，
        if (xlsPath.equals("")) {
            throw new IOException("文件路径不能为空！");
        } else {
            File file = new File(xlsPath);
            if (!file.exists()) {
                throw new IOException("文件不存在！");
            }
        }
        //获取扩展名
        String ext = xlsPath.substring(xlsPath.lastIndexOf(".") + 1);
        try {
            if ("xls".equals(ext)) {                //使用xls方式读取
                return readExcel_xls(xlsPath);
            } else if ("xlsx".equals(ext)) {        //使用xlsx方式读取
                return readExcel_xlsx(xlsPath);
            } else {                                    //依次尝试xls、xlsx方式读取
                System.out.println("您要操作的文件没有扩展名，正在尝试以xls方式读取...");
                try {
                    return readExcel_xls(xlsPath);
                } catch (IOException e1) {
                    System.out.println("尝试以xls方式读取，结果失败！，正在尝试以xlsx方式读取...");
                    try {
                        return readExcel_xlsx(xlsPath);
                    } catch (IOException e2) {
                        System.out.println("尝试以xls方式读取，结果失败！\n请您确保您的文件是Excel文件，并且无损，然后再试。");
                        throw e2;
                    }
                }
            }
        } catch (IOException e) {
            throw e;
        }
    }

    public List<Row> readExcel_xls(String xlsPath) throws IOException {

        // 判断文件是否存在
        File file = new File(xlsPath);
        if (!file.exists()) {
            throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
        }

        // 用于Workbook级的操作，创建、删除Excel
        List<Row> rowList = new ArrayList<Row>();

        try {
            // 读取Excel
            this.workbook = new HSSFWorkbook(new FileInputStream(file));

            // 读取Excel 97-03版，xls格式
            rowList = readExcel(workbook);

        } catch (IOException e) {
            e.printStackTrace();
        }
        return rowList;
    }

    public List<Row> readExcel_xlsx(String xlsPath) throws IOException {
        // 判断文件是否存在
        File file = new File(xlsPath);
        if (!file.exists()) {
            throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
        }

        List<Row> rowList = new ArrayList<Row>();
        try {
            FileInputStream fis = new FileInputStream(file);
            // 去读Excel
            this.workbook = new XSSFWorkbook(fis);

            // 读取Excel 2007版，xlsx格式
            rowList = readExcel(workbook);

        } catch (IOException e) {
            e.printStackTrace();
        }
        return rowList;
    }

    private List<Row> readExcel(Workbook wb) {
        List<Row> rowList = new ArrayList<Row>();

        int sheetCount = 1;//需要操作的sheet数量

        Sheet sheet = null;
        if (onlyReadOneSheet) {    //只操作一个sheet
            // 获取设定操作的sheet(如果设定了名称，按名称查，否则按索引值查)
            this.sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
            sheet = this.sheet;
        } else {                            //操作多个sheet
            sheetCount = wb.getNumberOfSheets();//获取可以操作的总数量
        }

        // 获取sheet数目
        for (int t = startSheetIdx; t < sheetCount + endSheetIdx; t++) {
            // 获取设定操作的sheet
            if (!onlyReadOneSheet) {
                sheet = wb.getSheetAt(t);
            }

            //获取最后行号
            int lastRowNum = sheet.getLastRowNum();

            if (lastRowNum > 0) {    //如果>0，表示有数据
                out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：");
            }

            Row row = null;
            // 循环读取
            for (int i = startReadPos; i <= lastRowNum + endReadPos; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    rowList.add(row);
                    out("第" + (i + 1) + "行：", false);
                    // 获取每一单元格的值
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        String value = getCellValue(row.getCell(j));
                        if (!value.equals("")) {
                            out(value + " | ", false);
                        }
                    }
                    out("",true);
                }
            }
        }
        return rowList;
    }

    private void writeExcel(Workbook wb, List<Row> rowList, String xlsPath) {

        if (wb == null) {
            out("操作文档不能为空！");
            return;
        }

        Sheet sheet = wb.getSheetAt(0);// 修改第一个sheet中的值

        // 如果每次重写，那么则从开始读取的位置写，否则果获取源文件最新的行。
        int lastRowNum = isOverWrite ? startReadPos : sheet.getLastRowNum() + 1;
        int t = 0;//记录最新添加的行数
        out("要添加的数据总条数为：" + rowList.size());
        for (Row row : rowList) {
            if (row == null) continue;
            // 判断是否已经存在该数据
            int pos = findInExcel(sheet, row);

            Row r = null;// 如果数据行已经存在，则获取后重写，否则自动创建新行。
            if (pos >= 0) {
                sheet.removeRow(sheet.getRow(pos));
                r = sheet.createRow(pos);
            } else {
                r = sheet.createRow(lastRowNum + t++);
            }

            //用于设定单元格样式
            CellStyle newstyle = wb.createCellStyle();

            //循环为新行创建单元格
            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                Cell cell = r.createCell(i);// 获取数据类型
                cell.setCellValue(getCellValue(row.getCell(i)));// 复制单元格的值到新的单元格
                // cell.setCellStyle(row.getCell(i).getCellStyle());//出错
                if (row.getCell(i) == null) continue;
                copyCellStyle(row.getCell(i).getCellStyle(), newstyle); // 获取原来的单元格样式
                cell.setCellStyle(newstyle);// 设置样式
                // sheet.autoSizeColumn(i);//自动跳转列宽度
            }
        }
        out("其中检测到重复条数为:" + (rowList.size() - t) + " ，追加条数为：" + t);

        // 统一设定合并单元格
        setMergedRegion(sheet);

        try {
            // 重新将数据写入Excel中
            FileOutputStream outputStream = new FileOutputStream(xlsPath);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            out("写入Excel时发生错误！ ");
            e.printStackTrace();
        }
    }

    public void setMergedRegion(Sheet sheet) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            // 获取合并单元格位置
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstRow = ca.getFirstRow();
            if (startReadPos - 1 > firstRow) {// 如果第一个合并单元格格式在正式数据的上面，则跳过。
                continue;
            }
            int lastRow = ca.getLastRow();
            int mergeRows = lastRow - firstRow;// 合并的行数
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            // 根据合并的单元格位置和大小，调整所有的数据行格式，
            for (int j = lastRow + 1; j <= sheet.getLastRowNum(); j++) {
                // 设定合并单元格
                sheet.addMergedRegion(new CellRangeAddress(j, j + mergeRows, firstColumn, lastColumn));
                j = j + mergeRows;// 跳过已合并的行
            }

        }
    }

    public static void copyCellStyle(CellStyle fromStyle, CellStyle toStyle) {
        toStyle.setAlignment(fromStyle.getAlignment());
        // 边框和边框颜色
        toStyle.setBorderBottom(fromStyle.getBorderBottom());
        toStyle.setBorderLeft(fromStyle.getBorderLeft());
        toStyle.setBorderRight(fromStyle.getBorderRight());
        toStyle.setBorderTop(fromStyle.getBorderTop());
        toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
        toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
        toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
        toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());

        // 背景和前景
        toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
        toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

        // 数据格式
        toStyle.setDataFormat(fromStyle.getDataFormat());
        toStyle.setFillPattern(fromStyle.getFillPattern());
        // toStyle.setFont(fromStyle.getFont(null));
        toStyle.setHidden(fromStyle.getHidden());
        toStyle.setIndention(fromStyle.getIndention());// 首行缩进
        toStyle.setLocked(fromStyle.getLocked());
        toStyle.setRotation(fromStyle.getRotation());// 旋转
        toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
        toStyle.setWrapText(fromStyle.getWrapText());

    }

    private int findInExcel(Sheet sheet, Row row) {
        int pos = -1;

        try {
            // 如果覆写目标文件，或者不需要比较，则直接返回
            if (isOverWrite || !isNeedCompare) {
                return pos;
            }
            for (int i = startReadPos; i <= sheet.getLastRowNum() + endReadPos; i++) {
                Row r = sheet.getRow(i);
                if (r != null && row != null) {
                    String v1 = getCellValue(r.getCell(comparePos));
                    String v2 = getCellValue(row.getCell(comparePos));
                    if (v1.equals(v2)) {
                        pos = i;
                        break;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pos;
    }

    public void writeExcel(List<Row> rowList, String xlsPath) throws IOException {

        //扩展名为空时，
        if (xlsPath.equals("")) {
            throw new IOException("文件路径不能为空！");
        }
        //获取扩展名
        String ext = xlsPath.substring(xlsPath.lastIndexOf(".") + 1);

        try {

            if ("xls".equals(ext)) {                //使用xls方式写入
                writeExcel_xls(rowList, xlsPath);
            } else if ("xlsx".equals(ext)) {        //使用xlsx方式写入
                writeExcel_xlsx(rowList, xlsPath);
            } else {                                    //依次尝试xls、xlsx方式写入
                out("您要操作的文件没有扩展名，正在尝试以xls方式写入...");
                try {
                    writeExcel_xls(rowList, xlsPath);
                } catch (IOException e1) {
                    out("尝试以xls方式写入，结果失败！，正在尝试以xlsx方式读取...");
                    try {
                        writeExcel_xlsx(rowList, xlsPath);
                    } catch (IOException e2) {
                        out("尝试以xls方式写入，结果失败！\n请您确保您的文件是Excel文件，并且无损，然后再试。");
                        throw e2;
                    }
                }
            }
        } catch (IOException e) {
            throw e;
        }
    }

    public void writeExcel_xls(List<Row> rowList, String dist_xlsPath) throws IOException {
        writeExcel_xls(rowList, excelPath, dist_xlsPath);
    }


    public void writeExcel_xls(List<Row> rowList, String src_xlsPath, String dist_xlsPath) throws IOException {

        // 判断文件路径是否为空
        if (dist_xlsPath == null || dist_xlsPath.equals("")) {
            out("文件路径不能为空");
            throw new IOException("文件路径不能为空");
        }
        // 判断文件路径是否为空
        if (src_xlsPath == null || src_xlsPath.equals("")) {
            out("文件路径不能为空");
            throw new IOException("文件路径不能为空");
        }

        // 判断列表是否有数据，如果没有数据，则返回
        if (rowList == null || rowList.size() == 0) {
            out("文档为空");
            return;
        }

        try {
            HSSFWorkbook wb = null;

            // 判断文件是否存在
            File file = new File(dist_xlsPath);
            if (file.exists()) {
                // 如果复写，则删除后
                if (isOverWrite) {
                    file.delete();
                    // 如果文件不存在，则创建一个新的Excel
                    // wb = new HSSFWorkbook();
                    // wb.createSheet("Sheet1");
                    wb = new HSSFWorkbook(new FileInputStream(src_xlsPath));
                } else {
                    // 如果文件存在，则读取Excel
                    wb = new HSSFWorkbook(new FileInputStream(file));
                }
            } else {
                // 如果文件不存在，则创建一个新的Excel
                // wb = new HSSFWorkbook();
                // wb.createSheet("Sheet1");
                wb = new HSSFWorkbook(new FileInputStream(src_xlsPath));
            }

            // 将rowlist的内容写到Excel中
            writeExcel(wb, rowList, dist_xlsPath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 修改Excel（97-03版，xls格式）
     *
     * @param rowList
     * @param dist_xlsPath
     * @throws IOException
     * @Title: writeExcel_xls
     * @Date : 2014-9-11 下午01:50:38
     */
    public void writeExcel_xlsx(List<Row> rowList, String dist_xlsPath) throws IOException {
        writeExcel_xls(rowList, excelPath, dist_xlsPath);
    }


    public void writeExcel_xlsx(List<Row> rowList, String src_xlsPath, String dist_xlsPath) throws IOException {

        // 判断文件路径是否为空
        if (dist_xlsPath == null || dist_xlsPath.equals("")) {
            out("文件路径不能为空");
            throw new IOException("文件路径不能为空");
        }
        // 判断文件路径是否为空
        if (src_xlsPath == null || src_xlsPath.equals("")) {
            out("文件路径不能为空");
            throw new IOException("文件路径不能为空");
        }

        // 判断列表是否有数据，如果没有数据，则返回
        if (rowList == null || rowList.size() == 0) {
            out("文档为空");
            return;
        }

        try {
            // 读取文档
            XSSFWorkbook wb = null;

            // 判断文件是否存在
            File file = new File(dist_xlsPath);
            if (file.exists()) {
                // 如果复写，则删除后
                if (isOverWrite) {
                    file.delete();
                    // 如果文件不存在，则创建一个新的Excel
                    // wb = new XSSFWorkbook();
                    // wb.createSheet("Sheet1");
                    wb = new XSSFWorkbook(new FileInputStream(src_xlsPath));
                } else {
                    // 如果文件存在，则读取Excel
                    wb = new XSSFWorkbook(new FileInputStream(file));
                }
            } else {
                // 如果文件不存在，则创建一个新的Excel
                // wb = new XSSFWorkbook();
                // wb.createSheet("Sheet1");
                wb = new XSSFWorkbook(new FileInputStream(src_xlsPath));
            }
            // 将rowlist的内容添加到Excel中
            writeExcel(wb, rowList, dist_xlsPath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public String getCellValue(Cell cell) {
        Object result = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    result = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            result = new SimpleDateFormat("yyyy-MM-dd")
                                    .format(date);
                        } else {
                            result = "";
                        }
                    } else {
                        result = new DecimalFormat("0").format(cell
                                .getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    result = cell.getCellFormula();
                    break;
                case Cell.CELL_TYPE_ERROR:
                    result = cell.getErrorCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                default:
                    break;
            }
        }
        return result.toString();
    }

    //向excel中追加内容
    public void appendFileContent(String filePath, Object[] content) throws Exception {

    }

    public int getFirstRowNum(String cellValue){
        int firstRow = 1;
        try {
            List<Row> list = readExcel();
            for(Row r : list){
                if(cellValue.equals(r.getCell(0))){
                   firstRow = r.getRowNum();
                    break;
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return firstRow;
    }

    public int getLastRowNum(String cellValue){
        int lastRow = -1;
        try {
            List<Row> list = readExcel();
            for(Row r : list){
                if(cellValue.equals(r.getCell(0))){
                    continue;
                }else{
                    lastRow = r.getRowNum();
                    break;
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return lastRow;
    }

    public List<String[]> getCells(String cellValue,String idxs){
        List<String[]> cc = new ArrayList<String[]>();
        try {
            List<Row> list = readExcel();
            for(Row r : list){
                if(StringUtils.isNotBlank(idxs)){
                    String[] ss = idxs.split(Constants.SPLIT_DOT);
                    String[] cells = new String[ss.length + 1];
                    int rowNum = r.getRowNum();
                    if(StringUtils.isNotBlank(cellValue)){
                        if(cellValue.equals(r.getCell(Integer.valueOf(Constants.STEP_CASE_IDX)).toString())){
                            for(int i = 0;i<ss.length; i++){
                                cells[i] = getCellValue(r.getCell(Integer.valueOf(ss[i])));
                            }
                            cells[cells.length-1] = String.valueOf(rowNum);
                            cc.add(cells);
                        }else{
                            continue;
                        }
                    }else{
                        for(int i = 0;i<ss.length; i++){
                            cells[i] = getCellValue(r.getCell(Integer.valueOf(ss[i])));
                        }
                        cells[cells.length-1] = String.valueOf(rowNum);
                        cc.add(cells);
                    }

                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return cc;
    }

    public List<Row> getRowsByIndex(int[] rowIdxs) throws Exception{
        Sheet sheet = getSheet();
        List<Row> rows = new ArrayList<Row>();
        for (int i = 0;i<rowIdxs.length;i++){
            rows.add(sheet.getRow( rowIdxs[i]));
        }
        return rows;
    }


    public Workbook getWorkBook(){
        String filePath = this.excelPath;
        String ext = filePath.substring(filePath.lastIndexOf(".") + 1);
        try {
            if ("xls".equals(ext)) {                //使用xls方式读取
                this.workbook = new HSSFWorkbook(new FileInputStream(new File(filePath)));
            } else if ("xlsx".equals(ext)) {        //使用xlsx方式读取
                this.workbook = new XSSFWorkbook(new FileInputStream(new File(filePath)));
            }
        }catch (Exception e){

        }
        return this.workbook;
    }

    public Sheet getSheet(){
        if (onlyReadOneSheet) {    //只操作一个sheet
            // 获取设定操作的sheet(如果设定了名称，按名称查，否则按索引值查)
            this.sheet = selectedSheetName.equals("") ? getWorkBook().getSheetAt(selectedSheetIdx) : getWorkBook().getSheet(selectedSheetName);
        }
        return this.sheet;
    }

    public void setCell(String value,int row,int col){
        try {
            Row r = getSheet().getRow(row);
            Cell c = r.getCell(col);
            c.setCellValue(value);
            FileOutputStream fos = new FileOutputStream(this.excelPath);
            getWorkBook().write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void setCells(String[] value,int row,int[] cols){
        try {
            Row r = getSheet().getRow(row);
            for(int i = 0;i<cols.length; i ++){
                Cell c = r.getCell(cols[i]);
                if(StringUtils.isBlank(getCellValue(c))){
                    c.setCellValue(value[i]);
                }
            }
            FileOutputStream fos = new FileOutputStream(this.excelPath);
            getWorkBook().write(fos);
            fos.flush();
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void clearCol(int col){
        for(int i = 1;i<=getSheet().getLastRowNum();i++){
            Row r = getSheet().getRow(i);
            Cell c = r.getCell(col);
            c.setCellValue("");
            try {
                FileOutputStream fos = new FileOutputStream(this.excelPath);
                getWorkBook().write(fos);
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private void out(String msg) {
        if (printMsg) {
            out(msg, true);
        }
    }

    /**
     * 打印消息，
     *
     * @param msg 消息内容
     * @param tr  换行
     */
    private void out(String msg, boolean tr) {
        if (printMsg) {
            System.out.print(msg + (tr ? "\n" : ""));
        }
    }

    public String getExcelPath() {
        return this.excelPath;
    }

    public void setExcelPath(String excelPath) {
        this.excelPath = excelPath;
    }

    public boolean isNeedCompare() {
        return isNeedCompare;
    }

    public void setNeedCompare(boolean isNeedCompare) {
        this.isNeedCompare = isNeedCompare;
    }

    public int getComparePos() {
        return comparePos;
    }

    public void setComparePos(int comparePos) {
        this.comparePos = comparePos;
    }

    public int getStartReadPos() {
        return startReadPos;
    }

    public void setStartReadPos(int startReadPos) {
        this.startReadPos = startReadPos;
    }

    public int getEndReadPos() {
        return endReadPos;
    }

    public void setEndReadPos(int endReadPos) {
        this.endReadPos = endReadPos;
    }

    public boolean isOverWrite() {
        return isOverWrite;
    }

    public void setOverWrite(boolean isOverWrite) {
        this.isOverWrite = isOverWrite;
    }

    public boolean isOnlyReadOneSheet() {
        return onlyReadOneSheet;
    }

    public void setOnlyReadOneSheet(boolean onlyReadOneSheet) {
        this.onlyReadOneSheet = onlyReadOneSheet;
    }

    public int getSelectedSheetIdx() {
        return selectedSheetIdx;
    }

    public void setSelectedSheetIdx(int selectedSheetIdx) {
        this.selectedSheetIdx = selectedSheetIdx;
    }

    public String getSelectedSheetName() {
        return selectedSheetName;
    }

    public void setSelectedSheetName(String selectedSheetName) {
        this.selectedSheetName = selectedSheetName;
    }

    public int getStartSheetIdx() {
        return startSheetIdx;
    }

    public void setStartSheetIdx(int startSheetIdx) {
        this.startSheetIdx = startSheetIdx;
    }

    public int getEndSheetIdx() {
        return endSheetIdx;
    }

    public void setEndSheetIdx(int endSheetIdx) {
        this.endSheetIdx = endSheetIdx;
    }

    public boolean isPrintMsg() {
        return printMsg;
    }

    public void setPrintMsg(boolean printMsg) {
        this.printMsg = printMsg;
    }
}

